from flask import Blueprint, render_template, request, redirect, url_for, flash, make_response, session, current_app
from function.sqlite_function import *
from time import localtime
import datetime
from datetime import datetime, timedelta

_analyse = Blueprint('analyse', __name__, url_prefix='/')


# 时间选择框

# 总库/大类/小类
# 折线图显示价格波动
# 正负柱状图显示进入金额

# 选择大类小类和操作人
# 提供可以打印记录的按钮
# 以正负柱状图显示该操作人在这个大类或者这个小类上的数量变化或者价格变化（二选一）


# 返回页面
@_analyse.route('/analyse')
def analyse1():
    # 大类预设数据
    con, cur = db_open()
    temp1 = sqliteObject_to_list_a(cur, f'''
        select distinct bigname from stateless_part
    ''')
    temp1 = sqliteObject_to_list_a(cur, f'''
        select * from bigname
    ''')
    add, reduce = make_sql(temp1, '2021-01-01', '2021-12-31')
    add = sqliteObject_to_list_h(cur, add)
    reduce = sqliteObject_to_list_h(cur, reduce)
    sql_num = []
    sql_price = []
    cmy = []
    return render_template('analyse/analyse.html', data=temp1, add=add, reduce=reduce, sql_num=sql_num,
                           sql_price=sql_price, cmy=cmy)


# 返回柱状图
@_analyse.route('/analyse2', methods=['POST'])
def analyse2():
    start = request.form.get('a')
    stop = request.form.get('b')
    bigname = request.form.get('c')
    smallname = request.form.get('d')
    con, cur = db_open()

    # time_list = time_eachday(start, stop)

    # 返回在该时间段内仓库总进出的情况
    if bigname == '' and smallname == '':
        temp1 = sqliteObject_to_list_a(cur, f'''
            select * from bigname
        ''')
        add, reduce = make_sql(temp1, start, stop)
        add = sqliteObject_to_list_h(cur, add)
        reduce = sqliteObject_to_list_h(cur, reduce)
        return render_template('analyse/analyse_pic1.html', add=add, reduce=reduce)
    if bigname != '' and smallname == '':
        temp2 = [bigname]
        add, reduce = make_sql(temp2, start, stop)
        add = sqliteObject_to_list_h(cur, add)
        reduce = sqliteObject_to_list_h(cur, reduce)
        return render_template('analyse/analyse_pic1.html', add=add, reduce=reduce)
    if bigname != '' and smallname != '':
        add, reduce = make_sql_small(bigname, smallname, start, stop)
        add = sqliteObject_to_list_h(cur, add)
        reduce = sqliteObject_to_list_h(cur, reduce)
        return render_template('analyse/analyse_pic1.html', add=add, reduce=reduce)


# 返回个数和价格走势
@_analyse.route('/analyse3', methods=['POST'])
def analyse3():
    start = request.form.get('a')
    start = start + ' ' + '00:00:00'
    stop = request.form.get('b')
    stop = stop + ' ' + '23:59:59'
    bigname = request.form.get('c')
    smallname = request.form.get('d')

    cur, con = db_open()

    # 数量变化
    sql_num = sqliteObject_to_list_h(cur, f'''
        select date, change 
        from '{bigname}' 
        where smallname = '{smallname}' and date >= '{start}' and date <= '{stop}'
        order by date
    ''')

    num = 0
    for i in range(len(sql_num)):
        num = num + sql_num[i][1]
        sql_num[i][1] = num
    print(sql_num)

    # 单价走势
    sql_price = sqliteObject_to_list_h(cur, f'''
        select date, unitprice 
        from '{bigname}' 
        where smallname = '{smallname}' and date >= '{start}' and date <= '{stop}'
        order by date
    ''')

    # 存储在库价值的数组
    cmy = []
    #根据前两个结果计算在库价值
    for i in range(len(sql_price)):
        money = sql_num[i][1] * sql_price[i][1]
        date = sql_num[i][0]
        cmy.append([date, money])


    return render_template('analyse/analyse_pic2.html', sql_num=sql_num, sql_price=sql_price, cmy=cmy)


# 制作拼接返回数据的sql
def make_sql(a, start, stop):
    cmy = []

    for i in a:
        temp1 = f"select DATE(date) as a, price as b from '{i}'"
        temp2 = " union all "
        cmy.append(temp1)
        cmy.append(temp2)
    cmy.pop()
    sql = ''
    for i in cmy:
        sql = sql + i
    sql_reduce = f'''
        select t.a, sum(t.b) from (
        {sql}
        ) as t
        where t.b < 0 and t.a >= '{start}' and t.a <= '{stop}'
        GROUP BY t.a
        ORDER BY t.a
    '''
    sql_add = f'''
        select t.a, sum(t.b) from (
        {sql}
        ) as t
        where t.b > 0 and t.a >= '{start}' and t.a <= '{stop}'
        GROUP BY t.a
        ORDER BY t.a
    '''
    return sql_add, sql_reduce


# 在明确指定小类后制作拼接返回数据的sql
def make_sql_small(bigname, smallname, start, stop):
    sql_reduce = f'''
        select t.a, sum(t.b) from (
        select DATE(date) as a, price as b from '{bigname}' where smallname = '{smallname}'
        ) as t
        where t.b < 0 and t.a >= '{start}' and t.a <= '{stop}'
        GROUP BY t.a
        ORDER BY t.a
    '''
    sql_add = f'''
        select t.a, sum(t.b) from (
        select DATE(date) as a, price as b from '{bigname}' where smallname = '{smallname}'
        ) as t
        where t.b > 0 and t.a >= '{start}' and t.a <= '{stop}'
        GROUP BY t.a
        ORDER BY t.a
    '''
    return sql_add, sql_reduce


# 输入两个时间，返回这两个时间中的所有日期
def time_eachday(start, end):
    start = datetime.strptime(start, "%Y-%m-%d").date()
    end = datetime.strptime(end, "%Y-%m-%d").date()

    temp = (end - start).days
    data = [start]
    temp1 = start
    for i in range(temp):
        temp1 = temp1 + timedelta(days=1)
        data.append(temp1)

    for j in range(len(data)):
        data[j] = data[j].isoformat()

    # 返回值 ['2021-02-27', '2021-02-28', '2021-03-01']
    return data


